package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import model.BorrowImf;
import model.User;
import util.StringUtil;

/**
 * 图书借阅信息Dao类
 * @author 梁
 *
 */
public class BorrowImfDao {
	/**
	 * 查询借阅信息集合
	 * @param con
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public ResultSet list(Connection con,BorrowImf borrowImf)throws Exception{
		String sql="select * from t_borrow where b_userName=? and b_passWord=? and b_bookName=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, borrowImf.getUserName());
		pstmt.setString(2, borrowImf.getPassWord());
		pstmt.setString(3, borrowImf.getBookName());
		return pstmt.executeQuery();
	}
		
	//图书管理员的借阅信息查询列表，查询条件不同
	public ResultSet list2(Connection con,BorrowImf borrowImf)throws Exception{
		
		StringBuffer stb=new StringBuffer("select * from t_borrow b,t_book bk where b.bookId=bk.id");
		if(StringUtil.isNotEmpty(borrowImf.getBookName())) {
			stb.append(" and b_bookName like '%"+borrowImf.getBookName()+"%'");
		}
		if(StringUtil.isNotEmpty(borrowImf.getUserName())) {
			stb.append(" and b_userName like '%"+ borrowImf.getUserName()+"%'");
		}
		
		PreparedStatement pstmt=con.prepareStatement(stb.toString());
		return pstmt.executeQuery();
//		String sql="select * from t_borrow where b_userName=? and b_bookName=?";
//		PreparedStatement pstmt=con.prepareStatement(sql);
//		pstmt.setString(1, borrowImf.getUserName());
//		pstmt.setString(2, borrowImf.getBookName());
//		return pstmt.executeQuery();
	}
	
	/**
	 * 查询借阅信息集合
	 * @param con
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public ResultSet list3(Connection con,BorrowImf borrowImf)throws Exception{
		String sql="select * from t_borrow where b_userName=? and b_passWord=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, borrowImf.getUserName());
		pstmt.setString(2, borrowImf.getPassWord());		
		return pstmt.executeQuery();
	}
	/**
	 * 借阅信息信息更新
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int update(Connection con,BorrowImf borrowImf) throws Exception{
		String sql="update t_borrow set b_userName =?,b_bookName=?,borrowTime=?,returnTime=?,renewNum=?,bookId=? ,b_status=? ,b_passWord=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,borrowImf.getUserName() );
		pstmt.setString(2, borrowImf.getBookName());
		pstmt.setDate(3, borrowImf.getBorrowTime());
		pstmt.setDate(4, borrowImf.getReturnTime());
		pstmt.setInt(5, borrowImf.getRenewNum());
		pstmt.setInt(6, borrowImf.getBookId());
		pstmt.setString(7, borrowImf.getStatus());	
		pstmt.setString(8, borrowImf.getPassWord());	
		pstmt.setInt(9, borrowImf.getId());	
		return pstmt.executeUpdate();
		
	}
	
	public int update2(Connection con,BorrowImf borrowImf) throws Exception{
		String sql="update t_borrow set returnTime=?,renewNum=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
//		String sql="update t_borrow set b_userName =?,b_bookName=?,borrowTime=?,returnTime=?,renewNum=?,bookId=? ,b_status=?  where id=?";
//		pstmt.setString(1,borrowImf.getUserName() );
//		pstmt.setString(2, borrowImf.getBookName());
//		pstmt.setDate(3, borrowImf.getBorrowTime());
//		pstmt.setDate(4, borrowImf.getReturnTime());
//		pstmt.setInt(5, borrowImf.getRenewNum());
//		pstmt.setInt(6, borrowImf.getBookId());
//		pstmt.setString(7, borrowImf.getStatus());		
//		pstmt.setInt(8, borrowImf.getId());	
		pstmt.setDate(1, borrowImf.getReturnTime());
		pstmt.setInt(2, borrowImf.getRenewNum());
		pstmt.setInt(3, borrowImf.getId());
		return pstmt.executeUpdate();
		
	}
	/**
	 * 图书借阅办理，添加图书借阅信息
	 * @param con
	 * @param borrowImf
	 * @return
	 * @throws Exception
	 */
	public int add(Connection con,BorrowImf borrowImf) throws Exception{
		String sql="insert into t_borrow values(null,?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, borrowImf.getUserName());
		pstmt.setString(2, borrowImf.getBookName());
		pstmt.setDate(3, borrowImf.getBorrowTime());
		pstmt.setDate(4, borrowImf.getReturnTime());
		pstmt.setInt(5,borrowImf.getRenewNum());
		pstmt.setInt(6,borrowImf.getBookId());
		pstmt.setString(7, borrowImf.getStatus());
		pstmt.setString(8, borrowImf.getPassWord());
		return pstmt.executeUpdate();
				
	}
	
	/**
	 * 图书借阅信息删除
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int delete(Connection con,String id)throws Exception{
		String sql="delete from t_borrow where bookId=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		return pstmt.executeUpdate();
				
	}
	
	
	
}
